with ord as (
  select
     id
     ,work_order_no
     ,customer_name
     ,customer_sex
     ,customer_phone
     ,customer_type
     ,receiver_province_name
     ,receiver_province_id
     ,receiver_city_name
     ,receiver_city_id
     ,receiver_area_name
     ,receiver_area_id
     ,receiver_detailed_address
     ,waybill_no
     ,first_type_id
     ,first_type_code
     ,first_type_name
     ,second_type_id
     ,second_type_code
     ,second_type_name
     ,second_type_reminder_count
     ,second_type_treatment_limitation
     ,problem_description
     ,emergency_level
     ,accept_network_id
     ,accept_network_code
     ,accept_network_name
     ,accept_network_type_id
     ,call_back_name
     ,call_back_phone
     ,reminder_count
     ,upgrade_status
     ,upgrade_count
     ,upgrade_path
     ,status
     ,source_code
     ,registration_network_id
     ,registration_network_code
     ,registration_network_name
     ,registration_network_type_id
     ,accept_by
     ,accept_by_code
     ,accept_by_name
     ,is_enable
     ,is_delete
     ,create_by
     ,update_by
     ,create_by_code
     ,update_by_code
     ,create_by_name
     ,update_by_name
     ,create_time
     ,update_time
     ,phone_count
     ,accept_time
     ,response_time
     ,upgrade_time
     ,sound_record_id
     ,total_duration
     ,duty_network_id
     ,duty_network_code
     ,duty_network_name
     ,duty_network_type_id
     ,record_group
     ,process_status
     ,service_code
     ,service_name
     ,service_registrant_code
     ,service_registrant_name
     ,old_accept_network_id
     ,old_accept_network_code
     ,old_accept_network_name
     ,old_accept_network_type_id
     ,accept_belong_network_id
     ,accept_belong_network_code
     ,accept_belong_network_name
     ,accept_belong_network_type_id
     ,response_state
     ,transfer_network_id
     ,transfer_network_code
     ,transfer_network_name
     ,transfer_network_type_id
     ,monitoring_time
     ,is_repeat
     ,accept_network_logo
     ,accept_league_network_id
     ,reg_league_network_id
     ,reg_network_logo
     ,reg_belong_network_id
     ,accept_franchisee_network_id
     ,accept_franchisee_network_code
     ,accept_franchisee_network_name
     ,response_network_id
     ,response_network_code
     ,response_network_name
     ,transfer_network_type
     ,1 as order_type
 from jms_ods.work_order
  where dt between date_sub('{{ execution_date | cst_ds }}',60) and date_add('{{ execution_date | cst_ds }}',60)

   union all

 select
      id
     ,work_order_no
     ,customer_name
     ,customer_sex
     ,customer_phone
     ,customer_type
     ,receiver_province_name
     ,receiver_province_id
     ,receiver_city_name
     ,receiver_city_id
     ,receiver_area_name
     ,receiver_area_id
     ,receiver_detailed_address
     ,waybill_no
     ,first_type_id
     ,first_type_code
     ,first_type_name
     ,second_type_id
     ,second_type_code
     ,second_type_name
     ,second_type_reminder_count
     ,second_type_treatment_limitation
     ,problem_description
     ,emergency_level
     ,accept_network_id
     ,accept_network_code
     ,accept_network_name
     ,accept_network_type_id
     ,call_back_name
     ,call_back_phone
     ,reminder_count
     ,upgrade_status
     ,upgrade_count
     ,upgrade_path
     ,null as status
     ,source_code
     ,registration_network_id
     ,registration_network_code
     ,registration_network_name
     ,registration_network_type_id
     ,accept_by
     ,accept_by_code
     ,accept_by_name
     ,is_enable
     ,is_delete
     ,create_by
     ,update_by
     ,create_by_code
     ,update_by_code
     ,create_by_name
     ,update_by_name
     ,create_time
     ,update_time
     ,phone_count
     ,accept_time
     ,response_time
     ,upgrade_time
     ,sound_record_id
     ,total_duration
     ,null as duty_network_id
     ,null as duty_network_code
     ,null as duty_network_name
     ,null as duty_network_type_id
     ,record_group
     ,process_status
     ,service_code
     ,service_name
     ,service_registrant_code
     ,service_registrant_name
     ,old_accept_network_id
     ,old_accept_network_code
     ,old_accept_network_name
     ,old_accept_network_type_id
     ,accept_belong_network_id
     ,accept_belong_network_code
     ,accept_belong_network_name
     ,accept_belong_network_type_id
     ,null as response_state
     ,null as transfer_network_id
     ,null as transfer_network_code
     ,null as transfer_network_name
     ,null as transfer_network_type_id
     ,monitoring_time
     ,is_repeat
     ,accept_network_logo
     ,accept_league_network_id
     ,reg_league_network_id
     ,reg_network_logo
     ,reg_belong_network_id
     ,null as accept_franchisee_network_id
     ,null as accept_franchisee_network_code
     ,null as accept_franchisee_network_name
     ,null as response_network_id
     ,null as response_network_code
     ,null as response_network_name
     ,null as transfer_network_type
     ,2 as order_type
 from jms_ods.claim_work_order
  where dt between date_sub('{{ execution_date | cst_ds }}',60) and date_add('{{ execution_date | cst_ds }}',60)
),
tmp as (
  select s.*,row_number() over(partition by s.work_order_no order by s.update_time desc) as rank
   from ord s
)
insert overwrite table jms_dwd.dwd_work_order_dt partition(dt)
select
     id
     ,work_order_no
     ,customer_name
     ,customer_sex
     ,customer_phone
     ,customer_type
     ,receiver_province_name
     ,receiver_province_id
     ,receiver_city_name
     ,receiver_city_id
     ,receiver_area_name
     ,receiver_area_id
     ,receiver_detailed_address
     ,waybill_no
     ,first_type_id
     ,first_type_code
     ,first_type_name
     ,second_type_id
     ,second_type_code
     ,second_type_name
     ,second_type_reminder_count
     ,second_type_treatment_limitation
     ,problem_description
     ,emergency_level
     ,accept_network_id
     ,accept_network_code
     ,accept_network_name
     ,accept_network_type_id
     ,call_back_name
     ,call_back_phone
     ,reminder_count
     ,upgrade_status
     ,upgrade_count
     ,upgrade_path
     ,status
     ,source_code
     ,registration_network_id
     ,registration_network_code
     ,registration_network_name
     ,registration_network_type_id
     ,accept_by
     ,accept_by_code
     ,accept_by_name
     ,is_enable
     ,is_delete
     ,create_by
     ,update_by
     ,create_by_code
     ,update_by_code
     ,create_by_name
     ,update_by_name
     ,create_time
     ,update_time
     ,phone_count
     ,accept_time
     ,response_time
     ,upgrade_time
     ,sound_record_id
     ,total_duration
     ,duty_network_id
     ,duty_network_code
     ,duty_network_name
     ,duty_network_type_id
     ,record_group
     ,process_status
     ,service_code
     ,service_name
     ,service_registrant_code
     ,service_registrant_name
     ,old_accept_network_id
     ,old_accept_network_code
     ,old_accept_network_name
     ,old_accept_network_type_id
     ,accept_belong_network_id
     ,accept_belong_network_code
     ,accept_belong_network_name
     ,accept_belong_network_type_id
     ,response_state
     ,transfer_network_id
     ,transfer_network_code
     ,transfer_network_name
     ,transfer_network_type_id
     ,monitoring_time
     ,is_repeat
     ,accept_network_logo
     ,accept_league_network_id
     ,reg_league_network_id
     ,reg_network_logo
     ,reg_belong_network_id
     ,accept_franchisee_network_id
     ,accept_franchisee_network_code
     ,accept_franchisee_network_name
     ,response_network_id
     ,response_network_code
     ,response_network_name
     ,transfer_network_type
     ,order_type
     ,date(create_time) as dt
  from tmp
    where rank = 1
     and date(create_time) between date_sub('{{ execution_date | cst_ds }}',60)
       and '{{ execution_date | cst_ds }}'
            distribute by pmod(id,3) ;